
import glob
import xlrd
import json
import io
import os

# config = {
#     "hero_data.xlsx": [  #大表名
#         {"hero_desc": #字表名
#         {"field": ["id", "ch_see"], #字段
#         "replace_field": {}, #需要替换字段名
#          "extra_field": {}, #额外增加的字段数据
#           "func": "excel_to_json" #处理函数
#           }
#           }
#     ],
# }
config = {

    "item_data.xlsx": [
        {"DATA1_物品表": {"field": ["base_id", "desc", "name", "use_type","lev","overlap"],
                        "field_type":{"base_id":"int","desc":"str","name":"str","use_type":"int","lev":"int","overlap":"int"},
                       "replace_field": {"lev":"req_lvl","overlap":"overlay"},
                       "extra_field": {},
                       "func": "excel_to_json",
                       "fileName": "item_base.json"}}
    ],
#     "activity_gift_snap_up_data.xlsx":[
#        {"DATA4 征战特惠礼包配置":{"field":["id","name","old_price","price","lev","award"],
#        "field_type":{"id":"int","name":"str","old_price":"int","price":"int","lev":"int","award":"str"},
#        "replace_field":{"price":"rmb","lev":"req_lvl","name":"show_name"},
#          "extra_field": {"class": 4},
#          "fileName":"recharge_sub_type_4.json",
#           "func": "excel_to_json",
#          }
#        }
#     ],

     "hero_data.xlsx":[
        {
            "DATA1 修士基础配置 ":{"field": ["base_id", "name","quality","is_core","realm" ],
                "field_type":{"base_id":"int","name":"str","quality":"int","is_core":"str","is_core":"int","realm":"int"},
                                                   "replace_field": {"base_id":"id"},
                                                 "func": "excel_to_json",
                                                  "fileName": "hero_data.json"}
        }
    ],
    "assets_data.xlsx":[
        {
            "DATA1 资产类型映射":{"field": ["label","type", "desc", ],
                                                   "field_type":{"label":"str","type":"int","desc":"str"},
                                                   "replace_field": {"type":"id","label":"name"},
                                                 "func": "excel_to_json",
                                                 "key_filed":"id",
                                                  "fileName": "assets_data.json"}
        }
    ],

    "charge_data.xlsx": [


             {"DATA1 充值配置": {"field": ["type", "id", "price", "name","query_award"],
                            "field_type":{"type":"int","id":"int","price":"int","name":"str","query_award":"str"},
                              "replace_field": {"type":"class","id":"type","price":"rmb","name":"show_name"},
                                  "func": "excel_to_json_recharge"}},
            {
                "充值类型": {
                    "field": ["class", "name"],
                    "field_type":{"class":"int","name":"str"},
                    "func": "excel_to_json",
                    "replace_field": {"class":"id"},
                    "extra_field": {},
                    "fileName":"recharge_type.json",
                }
            }
        ],
        "guide_data.xlsx":[
            {
                "DATA1 引导配置":
                {"field": [ "id", "name"],
                 "field_type":{"id":"int","name":"str"},
                 "func": "excel_to_json",
                 "fileName":"task_1.json",
                 }

            }
        ],
        "vip_data.xlsx":[
                    {
                        "DATA3 境界任务配置":
                        {"field": [ "task_id", "name"],
                         "field_type":{"task_id":"int","name":"str"},
                         "replace_field": {"task_id":"id"},
                         "func": "excel_to_json",
                         "fileName":"task_2.json",
                         }

                    }
                ],
                "main_guard_data.xlsx":[
                {"DATA1 主线关卡配置":
                {"field": [ "id", "name"],
                                         "field_type":{"id":"int","name":"str"},
                                         "func": "excel_to_json",
                                         "fileName":"task_3.json",
                                         }

                  }
                ]



    #"vip_data.xlsx": [
    #    {"vip_monthlycard": {"field": ["type", "price", "title_name"],
    #                         "replace_field": {"price": "rmb", "title_name": "show_name"},
    #                         "extra_field": {"class": 4},
    #                         "func": "excel_to_json",
    #                         "fileName": "recharge_sub_type_4.json"}}
    #],

}


def process_excel(excel_file, config):
    obj = __import__(__name__)
    print("开始处理%s \n" % excel_file)
    book = xlrd.open_workbook(excel_file)
    sheet_names = book.sheet_names()
    for index, sheet_name in enumerate(sheet_names):
        for tableConfig in config:
            for tableName, configObj in tableConfig.items():
                if sheet_name != tableName:
                    continue
#                 print(configObj)
                #                 print(tableName, configObj["func"],configObj["field"])
                sheet = book.sheet_by_index(index)  # 行数
                try:
                    funcName = configObj["func"]
                except KeyError:
                    assert ValueError("config error !!!!!!!")
                if hasattr(obj, funcName):
                    func = getattr(obj, funcName)
                    func(sheet, sheet_name, configObj)
                else:
                    assert ValueError("function not found  error !!!!!!!")


# 字典排序
def sort_dict(src=None):
    new_dict = dict()
    if src is None:
        return new_dict

    for i in sorted(src):
        new_dict[i] = src[i]
    return new_dict


# excel表格转json文件
def excel_to_json(sheet, sheet_name, config):
    max_row = sheet.nrows
    # 列数
    max_column = sheet.ncols
    print(" sheet_name:%s,max_row: %d, max_column: %d" % (sheet_name, max_row, max_column))
    # 结果，数组存储
    result = []
    heads = []
    data_type = []
    use_type = []
    allLineList = {}
    hindexList = []
    headConfig = config["field"]
    dtconfig = config["field_type"]
    key = config.get("key_filed", None)
    try:
        replace_config = config["replace_field"]
    except KeyError:
        replace_config = dict()

    try:
        extra_config = config["extra_field"]
    except KeyError:
        extra_config = dict()
    # 解析表头
    for hindex, column in enumerate(range(max_column)):
        # 读取的话行列是从（1，1）开始
        # header 字段名
        h = sheet.cell(1, column).value

        if h not in headConfig:
            continue
        heads.append(sheet.cell(1, column).value)
        dt = dtconfig[h]
        data_type.append(dt)
        # 数据类型
#         data_type.append(sheet.cell(1, column).value)
        # 区分前后端使用
#         use_type.append(sheet.cell(2, column).value)
        hindexList.append(hindex)

    # i = 1
    for row in range(2, max_row):
        firstValue = 0
        s = {}
        for i, hindex in enumerate(hindexList):
            if hindex == 0:
                first_cell = sheet.cell(row, 0)
                if first_cell.value is None or first_cell.value == "":
                    continue
                if key is None:
                    firstValue = int(first_cell.value)
            #     if firstValue ==0 or first_cell.value is None or first_cell.value == "":
            #         firstValue+=1
            # else:
            #     firstValue +=1
            desc = heads[i]
            try:
                desc = replace_config[desc]
            except KeyError:
                desc = heads[i]
            dtypes = data_type[i]
            if dtypes == 'int':
                try:
                    s[desc] = int(sheet.cell(row, hindex).value)
                except ValueError:
                    s[desc] = 0
            if dtypes == 'str':
                s[desc] = str(sheet.cell(row, hindex).value)
            if dtypes == 'atom':
                v = (str(sheet.cell(row, hindex).value)).split(".")
                if len(v) >=2:
                     v =v[1]
                else:
                    v = ""

                s[desc] = v
        # print(extra_config)
        for key, value in extra_config.items():
            s[key] = value
        
        if key is None:
            KEY = firstValue
        else:
            KEY = s[key]
        allLineList[str(KEY)] = sort_dict(s)
        del s
    try:
        saveFilename = config["fileName"]
    except KeyError:
        saveFilename = "./{}.json".format(sheet_name.split("#")[1])
    # 将json保存为文件
    if sheet_name == "DATA1 资产类型映射":
        print(allLineList)
    save_json_file(allLineList, saveFilename)


# recharge_data.xlsx
def excel_to_json_recharge(sheet, sheet_name, config):
    max_row = sheet.nrows
    # 列数
    max_column = sheet.ncols
    print(" sheet_name:%s,max_row: %d, max_column: %d" % (sheet_name, max_row, max_column))
    headConfig = config["field"]
    alldict = dict()
    # 行数
    max_row = sheet.nrows
    # 列数
    max_column = sheet.ncols
    # 结果，数组存储
    result = []
    heads = []
    data_type = []
    use_type = []
    dtconfig = config["field_type"]
    try:
        replace_config = config["replace_field"]
    except KeyError:
        replace_config = dict()
    # 解析表头
    for column in range(max_column):
        # 读取的话行列是从（1，1）开始
        # header 字段名
        h= sheet.cell(1, column).value
        # 数据类型

        heads.append(sheet.cell(1, column).value)
        try:
            dt = dtconfig[h]
        except KeyError:
            dt = 'str'
        data_type.append(dt)
        # 区分前后端使用
#         use_type.append(sheet.cell(2, column).value)
        # 遍历每一行
        # 解析表头
    # 遍历每一行
    for row in range(2, max_row):
        keyindex = 0
        one_line = {}
        # 获取每一行第一列
        first_cell = sheet.cell(row, 0)
        if first_cell.value == None or first_cell.value == "":
            continue
        for column in range(max_column):
            # 读取第二行开始每一个数据

            try:
                k = heads[column]
            except IndexError:
                continue

            oldk = heads[column]

#             print(headConfig,oldk)
#             print(column)
            if oldk not in headConfig:
                continue

            try:
                desc = replace_config[k]
            except KeyError:
                desc = heads[column]
            k = desc
            dt = dtconfig[oldk]


            cell = sheet.cell(row, column)
            value = cell.value
#             print('oldk',oldk,'value',value)
            if dt == "int":
#                 print('11111',dt,k,value,row,column,heads)
                try:
                    value = int(value)
                except Exception:
                    value = 0
                    print(row,column,value)
            if dt == "str":
                if value is None or value == "":
                    value = ""
                value = str(value)
#             print("k=value",k,value,row,column)
#             if oldk == 'id':
#                 print("id=>",value)
            if k == "class":
                if value =="":
                    value =0
                keyindex = int(value)

            one_line[str(k)] = value

#         print(one_line)
        try:
            old_line = alldict[keyindex]
        except KeyError:
            old_line = []
        old_line.append(sort_dict(one_line))
        alldict[keyindex] = old_line
    for index, index_c in alldict.items():
#         print(index)
        fileName = "recharge_sub_type_%s.json" % index
        tmpDict = dict()
        for c in index_c:
            tmpDict[c["type"]] = c
        save_json_file(tmpDict, fileName)


# 将json保存为文件
def save_json_file(jd, json_file_name):
    fileObj = io.open(json_file_name, "w", encoding="utf-8")
    # 把对象转化为json对象
    # indent: 参数根据数据格式缩进显示，读起来更加清晰
    # ensure_ascii = True：默认输出ASCII码，如果把这个该成False, 就可以输出中文。
    fileObj.write(json.dumps(jd, indent=4, ensure_ascii=False))
    fileObj.close()


if "__main__" == __name__:
    for fileName, configObj in config.items():
        if not os.path.exists(fileName):
            print("file:%s 文件不存在跳过" %fileName)
            continue
        process_excel(fileName, configObj)
    command = "/usr/local/php7/bin/php make_config.php"
    os.system(command)
#     b = os.popen(command,'r',1)
#     print(b)

